package com.example.lxrbf.db;

import java.util.Iterator;

import android.content.*;
import android.database.*;
import android.database.sqlite.*;
import android.util.Log;

public class MySQLiteController {
	private Context context = null; 
	private int version = 1;
	public static String dbname = "test.db",
							tablename = "test1";
	
	public MySQLiteController(Context context) {
		this.context = context;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname, null, version);
		helper.getReadableDatabase();
	}
	
	public int insert(String name, String[] phones) {
		int re = -1;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname, null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", name);
		
		for (int i = 0; i < phones.length; i++) {
			String phone = phones[i];
			values.put("phone"+(i+1), phone);
		}
		long n = db.insert(tablename, null, values);
		//Log.d("mytag", n + "插入");
		if (n != -1)
			re = 1;
		db.close();
		return re;
	}
	
	public int delete(String name) {
		int re = -1;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname, null ,version);
		SQLiteDatabase db = helper.getReadableDatabase();
		if(name == null)
			re = db.delete(tablename, null, null);
		re = db.delete(tablename, "name=?", new String[] {name});
		db.close();
		return re;
	}
	
	/**
	 * @param name
	 * @return id name phone1 phone2...phone8
	 */
	public String[] select(String name) {
		String[] ress = null;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname , null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from "+tablename+" where name=?",
				new String[] { name });
		if(cursor.getCount() == 1) {
			ress = new String[10];
		}else return null;
		while (cursor.moveToNext()) {
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name1 = cursor.getString(cursor.getColumnIndex("name"));
			for (int i = 0; i < ress.length-2; i++) {
				ress[i+2] = cursor.getString(cursor.getColumnIndex("phone"+(i+1)));
			}
			ress[0] = ""+id;
			ress[1] = name1;
			//只循环1次
		}
		// 关闭数据库连接
		db.close();
		return ress;
	}
	
	public String[] selectPhones(String name) {
		String[] ress = null;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname , null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from "+tablename+" where name=?",
				new String[] { name });
		if(cursor.getCount() == 1) {
			ress = new String[8];
		}else return null;
		while (cursor.moveToNext()) {
			for (int i = 0; i < ress.length; i++) {
				ress[i] = cursor.getString(cursor.getColumnIndex("phone"+(i+1)));
			}
			//只循环1次
		}
		// 关闭数据库连接
		db.close();
		return ress;
	}
	
	/**
	 * @return All names
	 */
	public String[] selectAll() {
		String[] ress = null;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname , null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from "+tablename, null);
		if(cursor.getCount() > 0)
			ress = new String[cursor.getCount()];
		int wl = 0;
		while (cursor.moveToNext()) {
			ress[wl] = cursor.getString(cursor.getColumnIndex("name"));
			wl++;
		}
		db.close();
		return ress;
	}
	
	
	
	public String[] getTableNames() {
		String[] names = null;
		int size = 0;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname, null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
			//"select name from sqlite_master where type='table' order by name"
		Cursor cursor = db.rawQuery(
				"select name from sqlite_master where type='table' order by name", null);  
		size = cursor.getCount();
		if(size==0)
			return null;
		names = new String[size];
		while(cursor.moveToNext()){  
			String name = cursor.getString(0);  
			names[cursor.getPosition()] = name;
			//Log.d("mytag", ""+cursor.getPosition()+" "+name);  
		} 
		db.close();
		return names;
	}
	
	public int deletTable(String tableName) {
		int re = 0;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname, null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
		try {
			db.execSQL("DROP TABLE "+tableName);
			re = 1;
		} catch (Exception e) {
			re = -1;
		}
		//re = db.delete("table1", null, null);//DROP TABLE mytab;
		db.close();
		return re;
	}
	
	public int createTable(String tableName) {
		int re = 0;
		MySQLiteOpenHelper helper = new MySQLiteOpenHelper(context, dbname, null, version);
		SQLiteDatabase db = helper.getReadableDatabase();
		try {
			db.execSQL(	"CREATE TABLE "+tableName+
					"(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE,"+
					"phone1 TEXT,"+"phone2 TEXT,"+"phone3 TEXT,"+"phone4 TEXT,"+
					"phone5 TEXT,"+"phone6 TEXT,"+"phone7 TEXT,"+"phone8 TEXT"+
					")");
			re = 1;
		} catch (Exception e) {
			re = -1;
		}
		db.close();
		return re;
	}
}
